 /*
Purpose: 	Clean 1987 Census of Government data 
Dataset: 	U.S. Dates of Municipal Incorporation 
Author: 	Kiara Wyndham-Douds
Date:		July 6, 2022
*/

/* This do file imports and prepares the 1987 Census of Governments File for merging
with other data sources. This file does not contain place FIPS codes, so I merge in  
a 2002 crosswalk - GOVS_ID_to_FIPS_Place_Codes_2002.xls - of Government IDs and place FIPS.
*/

**********************************
*IMPORT 1987 CENSUS OF GOVERNMENT DATA AND LIMIT TO MUNICIPALITIES AND NEEDED VARIABLES
**********************************

/*
Raw 1987 COG data were obtained from Census website: https://www.census.gov/programs-surveys/gov-finances/data/historical-data.html. 
This is the only year that COG included question about year of incorporation for municipalities.
Data come in Microsoft Access format, which I converted to Excel. 
Excel file with raw 1987 COG data: "QQ01_ General Purpose Data for 1987.xlsx"
Excel file with 1987 COG codebook: "4_Govt_Org_Directory_Surveys_Guide.xls" 
*/

import excel "QQ01_ General Purpose Data for 1987.xlsx", sheet("QQ01_ General Purpose Data for ") firstrow allstring

*limit to municpalities
codebook ID
*38,933 obs

/* Type Code
1-Digit Type of Government Code……………………………………
   1 = County government
   2 = Municipality government
   3 = Town or township government
*/

tab TypeCode,m
keep if TypeCode=="2"

*keep only needed vars
keep ID StateCode CountyCode Name FIPSCode_State FIPSCode_County FIPSCode_MSA FIPSCode_CMSA OriginalIncorporationDate HomeRuleCharter HomeRuleCharter YearHomeRuleAdopted FormofGovernment

save cog_1987_munis.dta, replace
clear

/* Note: COG data do not contain place FIPS codes. The COG zip file from the 
Census website includes a 2002 crosswalk for Government ID (the identifer in COG data) 
and place FIPS codes. This is the most recent crosswalk available to my knowledge.
I use it to obtain FIPS codes for municipalities. */

**********************************
*IMPORT 2002 GID TO PLACE FIPS XWALK
**********************************

import excel "GOVS_ID_to_FIPS_Place_Codes_2002_forstata.xls", sheet("Place Codes") firstrow allstring
*using 4_Govt_Org_Directory_Surveys_Guide.xls as codebook 


tab TypeCode,m
keep if TypeCode=="2"

codebook govid_14
*missing for 0

codebook placefips

distinct placefips
/*           |     total   distinct
-----------+----------------------
 placefips |     19429      14170
*/

sort placefips
quietly by placefips:  gen dup = cond(_N==1,0,_n)
tab dup
list placefips govid_9 govid_14 place_name countyfips statefips if dup>0
drop dup
*These placefips are missing state codes. Adding them

rename placefips placefips_short
gen placefips = statefips + placefips_short
codebook placefips
drop placefips_short

save govid_to_placefips_02.dta, replace
clear

**********************************
*MERGE XWALK WITH 1987 COG DATA
**********************************


/*Note: 191 municipalitiesin the 1987 COG data do not match with any places in 
the 2002 GOVID-to-PLACEFIPS crosswalk. These are likely municipalities that existed
in 1987 but not 2002 (due to merges, annexations, or other coding changes). In order
to retain these municpalities in the data, I manually matched all but 39 of them 
to 1990 Cesus places and merge in their 1990 Census Place FIPS codes (manual_placefips_cogs_1987.xls).*/


use cog_1987_munis.dta
rename ID govid_14

merge 1:1 govid_14 using govid_to_placefips_02.dta
* 19,009  matched
*191 not matched from master -  these are municipalites i manually match below to 1990 place FIPS codes
* 420 not matched from using 

sort statefips
list Name FIPSCode_State FIPSCode_County FIPSCode_MSA if _merge==1
*going to export and manually locate in 1990 (b/c closest to 1987) census data
*I hand coded the ones that matched (most did) and save as manual_placefips_cogs_1987.dta
drop if _merge==2
drop _merge

codebook Name
drop if govid_9=="262044001" // dropped because added in below in manual_placefips_cog_1987

**merge in manually coded
merge 1:1 Name FIPSCode_State FIPSCode_County  using manual_placefips_cog_1987.dta, update
*all matched from using
*39 missing b/c could not be manually matched
drop _merge

drop if placefips==""

save cog_1987_munis_wplacecodes.dta, replace
clear

**********************************
*MERGE IN NHGIS CODES SO CAN BE MATCHED ACROSS DECADES
**********************************

/*IPUMS NHGIS has created a place code identifer - NHGISPLACE  - that is consistent
over time. (https://www.nhgis.org/documentation/gis-data/place-points#identifiers)
I use the NHGIS Place Point 2000 file, which contains Census 2000 place FIPS codes - 
the closest in time to the 2002 crosswalk - and the NHGISPLACE codes. This match
will allow this data file to be matched to other place codes in other time periods. */

use nghis_gis_placepoint_00.dta //raw 2000 place point data obtained from IPUMS NHGIS (https://www.nhgis.org)
*25,150 places

codebook gisjoin

gen placefips_short = substr(gisjoin,5,5) 
gen statefips = substr(gisjoin,2,2) 

gen placefips= statefips + placefips_short
codebook placefips

merge 1:1 placefips using cog_1987_munis_wplacecodes.dta
*19,058 matched
*102 not matched from using
*6,092 not matched from master

sort statefips 
list statefips Name placefips if _merge==2
*decision: drop these 102 because not present in census place data

keep if _merge==3
drop _merge

*clean year of incorp var
codebook OriginalIncorporationDate
*missing for  3,315 municipalities (17.39% of municipalities)
*Decision: Remove from data. No other national dataset exists to my knowledge to supplement these missing data.

destring OriginalIncorporationDate, gen(year_incorp)
drop if year_incorp==.

*drop placefips b/c using NHGISPLACE variable to merge - don't want two geo id's
keep nhgisplace gisjoin govid_14 Name year_incorp HomeRuleCharter YearHomeRuleAdopted FormofGovernment govid_9


save cog_1987_munis_wplacecodes_wnhgiscode.dta, replace
clear

/*Note: These data contain year of incorporation for all municipalites in existence
in 1987. The Census of Government did not ask year of incorporation in subsequent 
years. To obtain information on more recently incorporated municipalities,
I add in data from the Census Boundary and Annexation Survey (BAS) - https://www.census.gov/programs-surveys/bas.html.
BAS data tables are highly accurate for new incorporations since its inception 
in 1982 but do not contain consistent information for municipalities incorporated 
prior to this. */












